#!/usr/bin/env python
# coding=utf-8
#__author__ = 'Yunchao Ling'

import MySQLdb
import time

time1 = time.time()

DATABASE_NAME = 'COSMIC_v70'
HOST = '10.10.31.17'
PORT = '3306'
USER_NAME = 'COSMIC'
PASSWORD = 'COSMIC'
CHAR_SET = 'utf8'

INFILE = "/local/data/backup/ycling/COSMIC/CosmicCodingMuts.vcf"

conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)

inputfile = open(INFILE, "r")
for line in inputfile:
    line = line.rstrip()
    if not line.startswith("#"):
        splitline = line.split("\t")
        chrom = splitline[0]
        pos = splitline[1]
        ref_id = splitline[2]
        ref = splitline[3]
        alt = splitline[4]
        qual = splitline[5]
        filt = splitline[6]
        info = splitline[7]
        detail = {"GENE": "", "STRAND": "", "CDS": "", "AA": "", "CNT": ""}
        if not info == ".":
            splitinfo = info.split(";")
            for item in splitinfo:
                splititem = item.split("=")
                name = splititem[0]
                value = splititem[1]
                detail[name] = value
        cursor = conn.cursor()
        sql = "insert into CosmicCodingMuts_v70_GRCh37_20141009 values(0,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        params = (
            chrom, pos, ref_id, ref, alt, qual, filt, info, detail["GENE"], detail["STRAND"], detail["CDS"],
            detail["AA"],
            detail["CNT"])
        result = cursor.execute(sql, params)
        conn.commit()
        cursor.close()
conn.close()
inputfile.close()

time2 = time.time()

print(time2 - time1)
